﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="DatabaseInfoBase.cs" company="SmartPlan">
//   SmartPlan 2011
// </copyright>
// <summary>
//   Defines the DatabaseInfoBase type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace DbDiff.Common.Model
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using Comparer;
    using DataAccess;
    using Helper;

    /// <summary>
    /// The database info base.
    /// </summary>
    public class DatabaseInfoBase 
    {
        #region fields

        /// <summary>
        /// The sql cmd.
        /// </summary>
        private DataSet metadataCommands;

        #endregion

        #region constructor
        
        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseInfoBase"/> class.
        /// </summary>
        public DatabaseInfoBase()
        {
            this.DatabaseDataSets = new DatabaseDataSets("DbObjectsDs");
            this.metadataCommands = this.ReadResourceData();
        }
        #endregion

        #region properties

        /// <summary>
        /// Gets or sets ConnectionString.
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// Gets or sets Server.
        /// </summary>
        public string Server { get; set; }

        /// <summary>
        /// Gets or sets Database.
        /// </summary>
        public string Database { get; set; }

        /// <summary>
        /// Gets or sets SqlObjectOptions.
        /// </summary>
        public SqlCompareOptions SqlObjectOptions { get; set; }

        /// <summary>
        /// Gets CurrentSqlVersion.
        /// </summary>
        public SqlVersionInternal CurrentSqlVersion
        {
            get;
            private set;
        }

        /// <summary>
        /// Gets or sets DatabaseDataSets.
        /// </summary>
        internal DatabaseDataSets DatabaseDataSets { get; set; }
        #endregion

        #region public functions
        /*
        /// <summary>
        /// The save.
        /// </summary>
        /// <param name="writer">
        /// The writer.
        /// </param>
        public void Save(XmlTextWriter writer)
        {
            writer.WriteAttributeString("ConnectionString", this.ConnectionString);
            writer.WriteAttributeString("Server", this.Server);
            writer.WriteAttributeString("Database", this.Database);
            writer.WriteAttributeString("SqlVersion", this.CurrentSqlVersion.ToString());

            writer.WriteAttributeString("User", this.user);
            writer.WriteAttributeString("Password", this.password);
            writer.WriteAttributeString("AuthType", this.authType.ToString());

            writer.WriteStartElement("CompareOptions");
            writer.WriteString(SerializationHelper.XmlSerialize(this.SqlObjectOptions));
            this.SqlObjectOptions.GetXml(writer);
            writer.WriteEndElement();

            // CompareOptions
            var sw = new StringWriter();
            writer.WriteStartElement("SqlCmd");


            // sqlCmd.WriteXml(sw, XmlWriteMode.WriteSchema);
            this.metadataCommands.WriteXml(sw, XmlWriteMode.IgnoreSchema);
            sw.Flush();
            writer.WriteCData(sw.ToString());
            sw.Close();
            writer.WriteEndElement(); // sqlCmd


            var hiddenColumns = new StringCollection();

            // visszaállítja a mappingType-okat defaultra
            for (int i = 0; i < this.DatabaseDataSets.Tables.Count; i++)
            {
                for (int j = 0; j < this.DatabaseDataSets.Tables[i].Columns.Count; j++)
                {
                    if (this.DatabaseDataSets.Tables[i].Columns[j].ColumnMapping == MappingType.Element)
                    {
                        continue;
                    }

                    hiddenColumns.Add(this.DatabaseDataSets.Tables[i].TableName + "|" +
                                      this.DatabaseDataSets.Tables[i].Columns[j].ColumnName);
                    this.DatabaseDataSets.Tables[i].Columns[j].ColumnMapping = MappingType.Element;
                }
            }

            sw = new StringWriter();
            writer.WriteStartElement("DtObjects");
            this.DatabaseDataSets.WriteXml(sw, XmlWriteMode.WriteSchema);

            // dtObjects.WriteXml(sw, XmlWriteMode.IgnoreSchema);
            sw.Flush();
            writer.WriteCData(sw.ToString());
            sw.Close();
            writer.WriteEndElement(); // dtObjects

            // visszatölti a hidden mappingType -okat
            foreach (string t in hiddenColumns)
            {
                string[] columnInfo = t.Split(new[] { '|' });
                this.DatabaseDataSets.Tables[columnInfo[0]].Columns[columnInfo[1]].ColumnMapping = MappingType.Hidden;
            }
        }

        /// <summary>
        /// The load.
        /// </summary>
        /// <param name="objNode">
        /// The db obj node.
        /// </param>
        /// <param name="compareOptions">
        /// The compare options.
        /// </param>
        /// <exception cref="ArgumentNullException">
        /// </exception>
        /// <exception cref="ApplicationException">
        /// </exception>
        /// <exception cref="ApplicationException">
        /// </exception>
        /// <exception cref="ApplicationException">
        /// </exception>
        /// <exception cref="ApplicationException">
        /// </exception>
        public void Load(XmlNode objNode, SqlCompareOptions compareOptions)
        {
            string stringValue;
            StringReader sr;

            if (objNode == null)
            {
                throw new ArgumentNullException("dbObjNode");
            }

            this.ConnectionString = XmlHelper.GetAttributeValue(objNode, "ConnectionString");
            this.Server = XmlHelper.GetAttributeValue(objNode, "Server");
            this.Database = XmlHelper.GetAttributeValue(objNode, "Database");

            this.user = XmlHelper.GetAttributeValue(objNode, "User");
            this.password = XmlHelper.GetAttributeValue(objNode, "Password");
            try
            {
                stringValue = XmlHelper.GetAttributeValue(objNode, "AuthType");
                this.authType = (AuthType)Enum.Parse(typeof(AuthType), stringValue, true);
            }
            catch
            {
                throw new ApplicationException("Invalid or empty DbObjects.AuthType");
            }

            try
            {
                stringValue = XmlHelper.GetAttributeValue(objNode, "SqlVersion");
                this.CurrentSqlVersion = (SqlVersionInternal)Enum.Parse(typeof(SqlVersionInternal), stringValue, true);
            }
            catch
            {
                throw new ApplicationException("Invalid or empty DbObjects.SqlVersion");
            }

            XmlNodeList optionNodes = objNode.SelectNodes("CompareOptions/Option");
            this.SqlObjectOptions = compareOptions;
            // this.SqlObjectOptions.Load(optionNodes);

            XmlNode node = objNode.SelectSingleNode("SqlCmd");
            if (node != null)
            {
                sr = new StringReader(node.InnerText);
                this.metadataCommands = new DataSet();
                this.metadataCommands.ReadXml(sr, XmlReadMode.IgnoreSchema);
                sr.Close();
            }
            else
            {
                throw new ApplicationException("Invalid or empty DbObjects.SqlCmd");
            }

            node = objNode.SelectSingleNode("DtObjects");
            if (node == null)
            {
                throw new ApplicationException("Invalid or empty DbObjects.DtObjects");
            }

            sr = new StringReader(node.InnerText);
            this.DatabaseDataSets = new DatabaseDataSets("DbObjectsDs")
            {
                EnforceConstraints = false
            };
            this.DatabaseDataSets.ReadXml(sr, XmlReadMode.ReadSchema);

            // dtObjects.ReadXml(sr, XmlReadMode.IgnoreSchema);
            this.DatabaseDataSets.EnforceConstraints = true;
            sr.Close();
        }
        */

        /// <summary>
        /// The set cn string.
        /// </summary>
        /// <param name="server">
        /// The server.
        /// </param>
        /// <param name="database">
        /// The database.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        /// <param name="userPassword">
        /// The password.
        /// </param>
        /// <param name="atype">
        /// The authentication type.
        /// </param>
        /// <exception cref="Exception">
        /// </exception>
        public void SetConnectionnString(string server, string database, string userName, string userPassword, AuthType atype)
        {
            this.Server = server;
            this.Database = database;
            this.ConnectionString = DatabaseConnectString.GetConnectString(server, database, userName, userPassword, atype);
            this.SetSqlVersion();            
        }

        /// <summary>
        /// The objects count.
        /// </summary>
        /// <returns>
        /// Return the datatables
        /// </returns>
        public int CountDataTableRows()
        {
            return this.DatabaseDataSets.Tables.Cast<DataTable>().Sum(tbl => tbl.Rows.Count);
        }

        #endregion

        #region internal
        /// <summary>
        /// The get sql command.
        /// </summary>
        /// <param name="databaseObjectTypes">
        /// The object type
        /// </param>
        /// <param name="sqlVersion">
        /// The sql version.
        /// </param>
        /// <param name="where">
        /// The where filter.
        /// </param>
        /// <returns>
        /// Return sql command text
        /// </returns>
        internal string GetSqlCommand(DatabaseObjectTypes databaseObjectTypes, SqlVersionInternal sqlVersion, string where)
        {
            string s = this.GetSqlCommand(databaseObjectTypes.ToString(), sqlVersion);

            if (this.SqlObjectOptions.GetCompareOptionBoolValue(OptionNames.IsCompareUnderscored))
            {
                s.Replace("#_", " ");
                s.Replace("_#", " ");
                return s;
            }

            int i = s.IndexOf("#_");
            int j = s.IndexOf("_#");
            if (i > -1 && j > -1)
            {
                string o = s.Substring(0, i);
                string p = s.Substring(j + 2);
                s = string.Format("{0} {1}", o, p);
            }
            
            return s;
        }

        /// <summary>
        /// The get sql command.
        /// </summary>
        /// <param name="dbobjectsTypes">
        /// The dbobjects table.
        /// </param>
        /// <param name="sqlVersion">
        /// The sql version.
        /// </param>
        /// <returns>
        /// The sql command string
        /// </returns>
        internal string GetSqlCommand(DatabaseObjectTypes dbobjectsTypes, SqlVersionInternal sqlVersion)
        {
            return this.GetSqlCommand(dbobjectsTypes, sqlVersion, string.Empty);
        }
        #endregion

        #region private functions

        /// <summary>
        /// Load the resource Sql command
        /// </summary>
        /// <returns>
        /// The resource dataset
        /// </returns>
        private DataSet ReadResourceData()
        {
            const string ResourceLocation = "DbDiff.Common.DataAccess.SqlCommand{0}.xml";
            this.metadataCommands = new DataSet("SqlCommand");
            var sr = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(string.Format(ResourceLocation, (int)SqlVersionInternal.Sql7));
            this.metadataCommands.ReadXml(sr);
            if (sr != null)
            {
                sr.Close();
                for (int i = 1; i < 5; i++)
                {
                    var sqlCmdInner = new DataSet("SqlCommand");
                    string s = string.Format(ResourceLocation, (int)SqlVersionInternal.Sql7 + i);
                    sr = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(s);
                    if (sr != null)
                    {
                        sqlCmdInner.ReadXml(sr);
                        sr.Close();
                    }

                    for (int j = 0; j < sqlCmdInner.Tables[0].Rows.Count; j++)
                    {
                        this.metadataCommands.Tables[0].ImportRow(sqlCmdInner.Tables[0].Rows[j]);
                    }
                }
            }

            return this.metadataCommands;
        }

        /// <summary>
        /// The set sql version.
        /// </summary>
        /// <exception cref="NullReferenceException">
        /// </exception>
        private void SetSqlVersion()
        {
            using (var connection = new SqlConnection(this.ConnectionString))
            {                    
                connection.Open();

                if (connection.ServerVersion != null)
                {
                    SqlVersionInternal maxValue = Enum.GetValues(typeof(SqlVersionInternal)).Cast<SqlVersionInternal>().Max();
                    int ver = Convert.ToInt32(connection.ServerVersion.Split('.')[0]);
                    if (ver > (int)maxValue)
                    {
                        this.CurrentSqlVersion = maxValue;
                    }
                    else
                    {
                        if (!Enum.IsDefined(typeof(SqlVersionInternal), ver))
                        {
                            throw new Exception(string.Format("This is not valid sql server: {0}", connection.ServerVersion));
                        }
                        this.CurrentSqlVersion = (SqlVersionInternal)ver;   
                    }
                    
                }
                else
                {
                    throw new NullReferenceException("connection.ServerVersion is null");
                }

                connection.Close();
            }
        }

        /// <summary>
        /// Get the sql command.
        /// </summary>
        /// <param name="objectsTable">
        /// The db objects table.
        /// </param>
        /// <param name="sqlVersion">
        /// The sql version.
        /// </param>
        /// <returns>
        /// The sql command.
        /// </returns>
        /// <exception cref="Exception">
        /// </exception>
        private string GetSqlCommand(string objectsTable, SqlVersionInternal sqlVersion)
        {
            DataRow[] dr = this.metadataCommands.Tables[0].Select(string.Format("Type='{0}' AND Version='{1}'", objectsTable, sqlVersion));
            if (dr.Length == 0)
            {                
                throw new Exception(objectsTable);
            }

            var s = dr[0]["Select"] as string;
            s += string.Format(" {0} ", (dr[0]["Where"] as string));
            s += string.Format(" {0} ", (dr[0]["Order"] as string));
            return s;
        }

        #endregion
    }
}
